MYDB=$DB_RAIL_STRIKES

###################################################################
### Enter strikes manually (based on media coverage)

sqlite3 $MYDB " CREATE TABLE strikes( \
  startDate TEXT, \
  endDate TEXT);"
	
sqlite3 $MYDB "insert into strikes values ('2013-06-13', '2013-06-13');"
sqlite3 $MYDB "insert into strikes values ('2013-09-10', '2013-09-10');"
sqlite3 $MYDB "insert into strikes values ('2013-12-12', '2013-12-12');"
##############################################################################################################

# Merge with time referentiel to get dayNum of startDate and dayNum of endDate
sqlite3 $MYDB "create table strikes2 as \
    select b.dayNum as startDay, c.dayNum as endDay, a.startDate, a.endDate \
    from strikes a, mydays b, mydays c \
    where a.startDate = b.date and a.endDate = c.date;"
dropTables $MYDB strikes
renameTable $MYDB strikes2 strikes

# Get strike days
sqlite3 $MYDB "create table strike_days as \
    select distinct a.date, a.dayNum, a.weekNum, a.yearMonth, 1 as isStrikeDay \
    from mydays a, strikes b \
    where a.dayNum >= b.startDay and a.dayNum <= b.endDay;"

# Merge with days table to get all fields
leftJoin $MYDB 1 strike_days2 mydays strike_days dayNum
sqlite3 $MYDB "update strike_days2 set isStrikeDay = 0 where isStrikeDay is null;"
selectColumns $MYDB 1 strike_days3 strike_days2 dayNum weekNum yearMonth isStrikeDay
dropTables $MYDB strike_days strike_days2
renameTable $MYDB strike_days3 strike_days

# Aggregate
for TIME_LEVEL in week day ; do
    echo "${TIME_LEVEL}..."
    if [ $TIME_LEVEL == "day" ]; then TIME_ID=dayNum; fi
    if [ $TIME_LEVEL == "week" ]; then TIME_ID=weekNum; fi
    
    sqlite3 $MYDB "create table tmp_$TIME_LEVEL as \
        select $TIME_ID, sum(isStrikeDay) as NumStrikeDays \
        from strike_days \
        group by $TIME_ID;"
    
    sqlite3 $MYDB "create table SNCF_$TIME_LEVEL as select a.$TIME_ID, b.NumStrikeDays \
        from Scope_$TIME_LEVEL a left join tmp_$TIME_LEVEL b on a.$TIME_ID=b.$TIME_ID;"

    dropTables $MYDB tmp_$TIME_LEVEL
done


### Clean up
dropTables $MYDB strikes strike_days
for TIME_LEVEL in week day ; do
    dropTables $MYDB Scope_${TIME_LEVEL}
done
dropTables $MYDB mydays
sqlite3 $MYDB "VACUUM;"
